package top.went.db.dao;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import top.went.pojo.PlanPayBackEntity;
import top.went.pojo.PlanPayDetailEntity;
import top.went.vo.PlanPayBackVO;

import java.util.List;

/**
 * 计划回款dao
 */
public interface PlanPayBackDao extends JpaRepository<PlanPayBackEntity,Integer> {
    /**
     * 查询所有计划回款
     * @param delete
     * @param pageable
     * @return
     */
    public List<PlanPayBackEntity> findAllByMagicDeleteOrderByPpbIdDesc(Long delete, Pageable pageable);

    /**
     * 根据回款日期查询所有回款记录
     * @param ppb_date
     * @return
     */
    @Query(nativeQuery = true,value = "SELECT * FROM TB_PLAN_PAY_BACK  WHERE  to_char(PPB_DATE,'yyyy-MM-dd') like ?1 and MAGIC_DELETE=0 order by ?#{#pageable}",
            countQuery = "SELECT count(*) FROM TB_PLAN_PAY_BACK WHERE to_char(PPB_DATE,'yyyy-MM-dd') like ?1 and magic_delete=0")
    public Page<PlanPayBackEntity> findAllByDate(String ppb_date, Pageable pageable);

    /**
     * 查询所有付款数量
     * @return
     */
    @Query(nativeQuery = true,value = "select count(*) from TB_PLAN_PAY_BACK where magic_delete=0 order by PPB_ID desc")
    public int ppb_findAllCount();

    /**
     * 根据付款日期查询所有付款数量
     * @param ppb_date
     * @return
     */
    @Query(nativeQuery = true,value = "SELECT count(*) FROM TB_PLAN_PAY_BACK WHERE  to_char(PPB_DATE,'yyyy-MM-dd') like ?1 and MAGIC_DELETE=0 order by PPB_ID desc")
    public int ppb_findByPpdDateCount(String ppb_date);

    /**
     * 加载
     * @param delete
     * @param ppbId
     * @return
     */
    @Query(nativeQuery = true,value = "SELECT * FROM TB_PLAN_PAY_BACK  WHERE   MAGIC_DELETE=?1 and PPB_ID=?2")
    public PlanPayBackEntity findAllByMagicDeleteAndPpbId(long delete,Integer ppbId);
    /**
     * 根据订单合同统计回款金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "select max(nvl(p.ORDER_TITLE,'其他')) TYPE, sum(nvl(o.PPB_MONEY,0)) count from TB_PLAN_PAY_BACK o left join TB_ORDER p on o.ORDER_ID=p.ORDER_ID group by o.ORDER_ID")
    List<Object[]> pbdstatisticsOrderCategoryMoney();
    /**
     * 根据年统计回款金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "select nvl(to_char(o.PPB_DATE,'yyyy'),'其他') TYPE, sum(nvl(o.PPB_MONEY,0)) count from TB_PLAN_PAY_BACK o group by to_char(o.PPB_DATE,'yyyy')")
    List<Object[]> pbdstatisticsByYear();
    /**
     * 根据月统计回款金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "select nvl(to_char(o.PPB_DATE,'yyyy-MM'),'其他') TYPE, sum(nvl(o.PPB_MONEY,0)) count from TB_PLAN_PAY_BACK o group by to_char(o.PPB_DATE,'yyyy-MM')")
    List<Object[]> pbdstatisticsByMonth();
    /**
     * 根据日期统计回款金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "    select nvl(to_char(o.PPB_DATE,'yyyy-MM-dd'),'其他') TYPE, sum(nvl(o.PPB_MONEY,0)) count from TB_PLAN_PAY_BACK o group by to_char(o.PPB_DATE,'yyyy-MM-dd')")
    List<Object[]> pbdstatisticsByDate();

    /**
     * 根据客户统计回款金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "    select max(nvl(p.CUS_NAME,'其他')) TYPE, sum(nvl(o.PPB_MONEY,0)) count from TB_PLAN_PAY_BACK o left join TB_CUSTOMER p on o.CUS_ID=p.CUS_ID group by o.CUS_ID")
    List<Object[]> pbdstatisticsByCus();
}
